--- title: SAIF.Business.View.Policy business rules summary: Enumerated business rules with per-rule trigger, enforcement, and outcomes for the policy-view and policy-authorization component. status: draft owner: SAIF Claims BOF team last_reviewed: 2026-08-21 library: SAIF.Business.View.Policy reference_type: embedded --- # SAIF.Business.View.Policy business rules This page captures business rules enforced by `SAIF.Business.View.Policy`. ## BR-VP-001 Valid policy number required | Field | Value | | --- | --- | | **Statement** | A policy number must be greater than zero to be considered valid. | | **Trigger** | Any retrieval/validation of a `clsPolicyData` object. | | **Enforcement** | `clsPolicyData.AddBusinessRules` → `Rules.AddRule(AddressOf ValidPolicyNum, "PolicyNum")`. | | **Outcome** | `IsValid` is `False` and a broken-rule message ("Error: Invalid policy number.") is attached when `PolicyNum <= 0`. | | **SQL** | [SQL-VP-008](sql/SQL-008-getpolicy.md) | | **Source** | `SAIF.Business.View.Policy\clsPolicyData.vb` | --- ## BR-VP-002 Secure-policy AD resource gate | Field | Value | | --- | --- | | **Statement** | Access to a policy flagged with the SAIF secure-policy code (`SecurePolicyCode = clsConstants.SAIF`) requires the current security principal to be authorized for the AD resource `SAIFpolicyInfo`. | | **Trigger** | `clsPolicySecurity.IsPolicyValidForUser(clsPolicyData)`. | | **Enforcement** | `clsPolicySecurity.vb` (`IsPolicyValidForUser`), calling `SecurityPrincipal.IsAuthorized("SAIFpolicyInfo")`. | | **Outcome** | Access denied (`False`) regardless of user type when the check fails. | | **Source** | `SAIF.Business.View.Policy\clsPolicySecurity.vb` | --- ## BR-VP-003 Policy validity and non-zero policy number required for access | Field | Value | | --- | --- | | **Statement** | A policy must be valid (`IsValid = True`) and have a non-zero policy number to be accessible. | | **Trigger** | `clsPolicySecurity.IsPolicyValidForUser`. | | **Enforcement** | `clsPolicySecurity.vb`. | | **Outcome** | Access denied when either condition fails. | | **Source** | `SAIF.Business.View.Policy\clsPolicySecurity.vb` | --- ## BR-VP-004 Unconditional access for internal staff user types | Field | Value | | --- | --- | | **Statement** | SAIF employees, MCO users, medical-provider users, and bookkeepers are always granted access to any (non-SAIF-secured) policy. | | **Trigger** | `IsPolicyValidForUser` `Select Case` on `UserType.UserType`. | | **Enforcement** | `clsPolicySecurity.vb`. | | **Outcome** | `hasAccess = True`. | | **Source** | `SAIF.Business.View.Policy\clsPolicySecurity.vb` | --- ## BR-VP-005 Agent ownership match required | Field | Value | | --- | --- | | **Statement** | An agent may access a policy only if the agent's user number matches the policy's agent number or the policy's agency sequence number. | | **Trigger** | `IsPolicyValidForUser` for `UserTypes.Agent`. | | **Enforcement** | `clsPolicySecurity.vb`. | | **Outcome** | Access granted only on a match. | | **Source** | `SAIF.Business.View.Policy\clsPolicySecurity.vb` | --- ## BR-VP-006 Policyholder ownership affiliation match required | Field | Value | | --- | --- | | **Statement** | A policyholder may access a policy only if it is their own policy or a policy for which they are recognized as a parent affiliate (per `Associations.Parents`). | | **Trigger** | `IsPolicyValidForUser` for `UserTypes.PolicyHolder`, delegating to `IsPolicyValidForParentPolicy`. | | **Enforcement** | `clsPolicySecurity.vb`. | | **Outcome** | Access granted only when the requested policy number equals the user's own number, or is found among the user's parent-affiliation associations. | | **Source** | `SAIF.Business.View.Policy\clsPolicySecurity.vb` | --- ## BR-VP-007 Group membership required | Field | Value | | --- | --- | | **Statement** | A group user may access a policy only if the policy is a member of that user's configured group/chapter, per the `cp_policy_in_group` stored procedure. | | **Trigger** | `IsPolicyValidForUser` for `UserTypes.Group`. | | **Enforcement** | `clsPolicySecurity.IsInGroup`. | | **Outcome** | Access granted only when the stored procedure returns a positive count. | | **SQL** | [SQL-VP-033](sql/SQL-033-isingroup.md) | | **Source** | `SAIF.Business.View.Policy\clsPolicySecurity.vb` | --- ## BR-VP-008 Loss-control consultant assignment match required | Field | Value | | --- | --- | | **Statement** | A loss-control consultant may access a policy only if they are the assigned Loss Control Consultant team member for that policy (matched by masked or unmasked username prefix). | | **Trigger** | `IsPolicyValidForUser` for `UserTypes.LossControl`. | | **Enforcement** | `clsPolicySecurity.vb` (`CanSafetyConsultantAccessPolicy`). | | **Outcome** | Access denied if no team assignment exists or the username does not match. | | **Source** | `SAIF.Business.View.Policy\clsPolicySecurity.vb` | --- ## BR-VP-009 Fee-auditor vendor policy audit-record match required | Field | Value | | --- | --- | | **Statement** | A fee auditor may access a policy only if there is a non-expired audit record (`PA_AUDIT`) for that policy associated with the auditor's vendor number. | | **Trigger** | `IsPolicyValidForUser` for `UserTypes.Auditor`. | | **Enforcement** | `clsPolicySecurity.CanAuditorAccessPolicy`. | | **Outcome** | Access granted only when the query returns a positive count. | | **Tables** | [PA_AUDIT](database-tables.md#pa_audit), [PA_AUDITOR](database-tables.md#pa_auditor), [PA_POLICY_PERIOD](database-tables.md#pa_policy_period) | | **SQL** | [SQL-VP-037](sql/SQL-037-canauditoraccesspolicy.md) | | **Source** | `SAIF.Business.View.Policy\clsPolicySecurity.vb` | --- ## BR-VP-010 Demo-user agency agent data masking | Field | Value | | --- | --- | | **Statement** | When displaying agency/agent data to a demo user (`UserAccessLevel` is one of the `Demo*` levels), non-blank agency/agent names other than `"SAIF"` are replaced with the literal `"SAIF APPOINTED AGENT"`, and contact fields (phone, address, email) are replaced with masking constants. | | **Trigger** | Any read of `clsAgency` properties (`AgencyName`, `AgentName`, `PhoneFormatted`, etc.) or `Fill`. | | **Enforcement** | `clsAgency.vb`. | | **Outcome** | PII is not exposed to demo users. | | **SQL** | [SQL-VP-001](sql/SQL-001-getagency.md) | | **Source** | `SAIF.Business.View.Policy\clsAgency.vb` | --- ## BR-VP-011 Demo-user address contact data masking | Field | Value | | --- | --- | | **Statement** | When displaying policy address/contact data to a demo user (`DemoAgent`/`DemoGroup`/`DemoPolicyholder`), contact name, phone, email, and Officer of Notice are replaced with masking constants (and the Officer of Notice lookup query is skipped entirely). | | **Trigger** | Read of `clsPolicyAddress.ContactName`/`ContactPhone`/`ContactEmailAddress`/`PolicyEmailAddress`/`OfficerOfNotice`. | | **Enforcement** | `clsPolicyAddress.vb`. | | **Outcome** | PII is not exposed to demo users; the Officer-of-Notice database round trip is avoided for demo users. | | **Tables** | [BUSINESS_AFFILIATE](database-tables.md#business_affiliate), [AFFILIATE](database-tables.md#affiliate) | | **SQL** | [SQL-VP-007](sql/SQL-007-retrieveofficerofnoticesql.md) | | **Source** | `SAIF.Business.View.Policy\clsPolicyAddress.vb` | --- ## BR-VP-012 Team-assignment masking preserves real username for authorization | Field | Value | | --- | --- | | **Statement** | When a team assignment's `MaskData` flag is set, the team member's username, full name, phone, and extension are replaced with masking constants, while the real username is preserved internally as `UserIdName` for downstream access checks (see [BR-VP-008](business-rules.md#br-vp-008-loss-control-consultant-assignment-match-required)). | | **Trigger** | `clsTeamAssignment.Fill`. | | **Enforcement** | `clsTeamAssignment.vb`. | | **Outcome** | Masked display values are exposed to callers, but authorization matching (loss-control consultant check) still uses the real username. | | **SQL** | [SQL-VP-024](sql/SQL-024-team_assign_query.md) | | **Source** | `SAIF.Business.View.Policy\clsTeamAssignment.vb` | --- ## BR-VP-013 Affiliated-policy agent agency filter | Field | Value | | --- | --- | | **Statement** | An affiliated policy is included in a `clsAffiliatedPolicyCollection` only if it matches the requested agent number/agency sequence-number filter (or if no filter, i.e. both are zero, is supplied). | | **Trigger** | `clsAffiliatedPolicyCollection.AfterRetrieve`. | | **Enforcement** | `clsAffiliatedPolicyCollection.vb`. | | **Outcome** | Rows not matching the agent/agency filter are excluded from the collection even though they were returned by the stored procedure. | | **SQL** | [SQL-VP-004](sql/SQL-004-getpolicyaffiliatelist.md) | | **Source** | `SAIF.Business.View.Policy\clsAffiliatedPolicyCollection.vb` | --- ## BR-VP-014 Non-employer account number must be non-zero | Field | Value | | --- | --- | | **Statement** | A non-employer account number must be non-zero to be considered valid. | | **Trigger** | Any retrieval/validation of `clsNonEmployerAccount`. | | **Enforcement** | `clsNonEmployerAccount.AddBusinessRules` → `Rules.AddRule(AddressOf IsValidAccountNumber, "AccountNumber")`. | | **Outcome** | `IsValid` is `False` when `AccountNumber = 0`. | | **SQL** | [SQL-VP-032](sql/SQL-032-non_empr_acct.md) | | **Source** | `SAIF.Business.View.Policy\clsNonEmployerAccount.vb` | --- --- ## BR-VP-015 Specific and current requests prefer policy-period employer-liability rows | Field | Value | | --- | --- | | **Statement** | Specific and current limit requests first read `POLICY_PERIOD_EMP_LIAB` rows joined to `EMPLOYERS_LIABILITY_LIMITS` when an in-effect override exists for the requested policy period. | | **Trigger** | `getCurrentLimitsForPolicyNum` or `getSpecificLimitsForPolicyNum` executes for a policy period with matching `POLICY_PERIOD_EMP_LIAB` rows. | | **Enforcement** | Each procedure opens the cursor with a first `SELECT` or `UNION` branch over `POLICY_PERIOD_EMP_LIAB` and `EMPLOYERS_LIABILITY_LIMITS` before fallback branches. | | **Outcome** | The returned cursor exposes policy-specific employer-liability limits instead of standard defaults. | | **Stored procedure cross-reference** | [coveragelimits_pkg.BR-VP-015](procedures/coveragelimits_pkg.md#br-vp-015-specific-and-current-requests-prefer-policy-period-employer-liability-rows) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\coveragelimits_pkg.json` | --- ## BR-VP-016 Limit retrieval falls back to standard factor rows when no specific override exists | Field | Value | | --- | --- | | **Statement** | If no qualifying policy-specific employer-liability row exists, the package returns standard limit rows where `standard_limits_flag = 'Y'` and the factor effective dates cover the evaluation date. | | **Trigger** | A current, specific, future, or all-limits lookup executes without a matching specific period override. | | **Enforcement** | Fallback branches join `EMPLOYERS_LIABILITY_LIMITS` to `EMPLOYERS_LIABILITY_FACTORS` and require `standard_limits_flag = 'Y'` plus `NOT EXISTS` checks against `POLICY_PERIOD_EMP_LIAB`. | | **Outcome** | Callers still receive formatted limit values even when a policy period has no explicit override row. | | **Stored procedure cross-reference** | [coveragelimits_pkg.BR-VP-016](procedures/coveragelimits_pkg.md#br-vp-016-limit-retrieval-falls-back-to-standard-factor-rows-when-no-specific-override-exists) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\coveragelimits_pkg.json` | --- ## BR-VP-017 Future and historical lookups use the policy periods own effective context | Field | Value | | --- | --- | | **Statement** | Future and older-period lookups compare effective-dated factor rows against the requested policy period or its expiration date rather than always using `SYSDATE`. | | **Trigger** | `getFutureLimitsForPolicyNum` or the historical branches inside `getSpecificLimitsForPolicyNum` execute. | | **Enforcement** | `getFutureLimitsForPolicyNum` compares adjustment windows to `pp.policy_period_eff_date`, and historical branches in `getSpecificLimitsForPolicyNum` use `PeriodEffDate`, `policy_period_exp_date`, or `policy_period_non_wcis` dates. | | **Outcome** | Returned limits reflect the requested future or historical policy period, not only the current-day configuration. | | **Stored procedure cross-reference** | [coveragelimits_pkg.BR-VP-017](procedures/coveragelimits_pkg.md#br-vp-017-future-and-historical-lookups-use-the-policy-periods-own-effective-context) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\coveragelimits_pkg.json` | --- ## BR-VP-018 Gap detection ignores flat-cancel and prospect periods | Field | Value | | --- | --- | | **Statement** | Gap detection ignores policy periods with status `FC` or `PPROS` before comparing one periods cancel or expiration date to the next periods effective date. | | **Trigger** | `getgaps` or `getgapindicator` executes. | | **Enforcement** | Both routines filter out `policy_period_status_code` values `FC` and `PPROS` and compare only the remaining periods. | | **Outcome** | Only meaningful coverage gaps are returned or counted. | | **Stored procedure cross-reference** | [pk72846_view_policy.BR-VP-018](procedures/pk72846_view_policy.md#br-vp-018-gap-detection-ignores-flat-cancel-and-prospect-periods) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` | --- ## BR-VP-019 Assigned-risk policy periods mask risk-type and plan data until the info page prints | Field | Value | | --- | --- | | **Statement** | For policy periods with status `RF`, the package suppresses `risk_type_code` and plan description values until `SP72347_INFO_PAGE_PRINTED_YN(policy_num, policy_period_eff_date)` returns `Y`. | | **Trigger** | `getpolicyperiod` or `getpolicyperiods` retrieves an `RF` policy period. | | **Enforcement** | Both procedures use `CASE` expressions that return empty strings for `RF` rows when `SP72347_INFO_PAGE_PRINTED_YN` is not `Y`. | | **Outcome** | Assigned-risk policy-period detail stays masked until the information page is printed. | | **Stored procedure cross-reference** | [pk72846_view_policy.BR-VP-019](procedures/pk72846_view_policy.md#br-vp-019-assigned-risk-policy-periods-mask-risk-type-and-plan-data-until-the-info-page-prints) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` | --- ## BR-VP-020 Claim-address requests may fall back to the policy address | Field | Value | | --- | --- | | **Statement** | If a claim-address request finds no active claim location and `ingetmailaddrwhennotfound = 'Y'`, the procedure changes the requested location code to `POL` before opening the cursor. | | **Trigger** | `getpolicyaddress` executes for address type `CLM` and no current `CLM` location exists. | | **Enforcement** | The procedure counts qualifying `CLM` locations, then assigns `v_addr_type := 'POL'` when fallback is enabled and the count is zero. | | **Outcome** | Callers receive the policy mailing or location address instead of an empty claim-address result. | | **Stored procedure cross-reference** | [pk72846_view_policy.BR-VP-020](procedures/pk72846_view_policy.md#br-vp-020-claim-address-requests-may-fall-back-to-the-policy-address) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` | --- ## BR-VP-021 Address retrieval prefers current contacts and the latest effective location row | Field | Value | | --- | --- | | **Statement** | Policy-address lookups join only active contacts, constrain location rows to the latest qualifying effective date, and return a single top-ranked row; location-sequence lookups also rank contact types before applying `ROWNUM <= 1`. | | **Trigger** | `getpolicyaddress` or `getpolicyaddressbylocseqnum` executes. | | **Enforcement** | `getpolicyaddress` filters `contact_exp_date IS NULL`, constrains the effective date with a `MAX(loc_eff_date)` subquery, and limits the result with `ROWNUM <= 1`; `getpolicyaddressbylocseqnum` orders contact types and also applies `ROWNUM <= 1`. | | **Outcome** | The package returns one current address and contact record rather than multiple stale rows. | | **Stored procedure cross-reference** | [pk72846_view_policy.BR-VP-021](procedures/pk72846_view_policy.md#br-vp-021-address-retrieval-prefers-current-contacts-and-the-latest-effective-location-row) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` | --- ## BR-VP-022 Affiliate-type classification follows a fixed hierarchy | Field | Value | | --- | --- | | **Statement** | Affiliate-type resolution checks for manual policyholder affiliations first, then wrap and leasing relationships, then subaccount and state-agency relationships, then experience-combination memberships, and finally premium-discount combinations. | | **Trigger** | `getpolicyaffiliatetype` executes. | | **Enforcement** | The function evaluates each affiliation family in sequence and returns immediately on the first matching `SELECT ... INTO`. | | **Outcome** | A policy receives the first matching affiliate classification in the packages explicit precedence order. | | **Stored procedure cross-reference** | [pk72846_view_policy.BR-VP-022](procedures/pk72846_view_policy.md#br-vp-022-affiliate-type-classification-follows-a-fixed-hierarchy) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` | --- ## BR-VP-023 Parent-policy resolution depends on the resolved affiliate type | Field | Value | | --- | --- | | **Statement** | Parent-policy resolution chooses the parent lookup strategy from the affiliate type returned by `getpolicyaffiliatetype`, including manual affiliation, relationship, combination, premium-discount, or self-parent fallback paths. | | **Trigger** | `getparentpolicy` executes. | | **Enforcement** | The function branches on `v_affil_type` and queries `policy_relationship`, `policy_relationship_policy_num`, `rate_account`, `combo_policy_period`, or the input policy number. | | **Outcome** | Callers receive the appropriate parent policy number for the resolved affiliation model. | | **Stored procedure cross-reference** | [pk72846_view_policy.BR-VP-023](procedures/pk72846_view_policy.md#br-vp-023-parent-policy-resolution-depends-on-the-resolved-affiliate-type) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` | --- ## BR-VP-024 Affiliate total rows are optional and vary by affiliation family | Field | Value | | --- | --- | | **Statement** | Affiliate lists add a single totals row only when `in_include_affil_totals = 'TRUE'`, and the totals label and selection logic vary by affiliate family. | | **Trigger** | `getpolicyaffiliatelist` executes. | | **Enforcement** | The procedure builds `v_totals_description` and `v_affil_type`, then includes totals-row `UNION` branches only when `UPPER(in_include_affil_totals) = 'TRUE'`. | | **Outcome** | Callers can opt into or suppress the family-specific totals row for affiliate displays. | | **Stored procedure cross-reference** | [pk72846_view_policy.BR-VP-024](procedures/pk72846_view_policy.md#br-vp-024-affiliate-total-rows-are-optional-and-vary-by-affiliation-family) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` | --- ## BR-VP-025 Wrap-up legal-name lookup climbs from member to relationship policy to rate account | Field | Value | | --- | --- | | **Statement** | When a wrap-up member references a different relationship policy, `getwrapuplegalname` first loads that policy legal name and then, if a rate account exists, replaces it with the rate-account policy legal name. | | **Trigger** | `getwrapuplegalname` executes for a wrap-up member policy. | | **Enforcement** | The function selects `policy_relationship_policy_num` and `legal_name`, then conditionally selects the relationship policy and finally the `rate_account` policy. | | **Outcome** | The returned legal name reflects the top-level wrap-up or rate-account owner when available. | | **Stored procedure cross-reference** | [pk72846_view_policy.BR-VP-025](procedures/pk72846_view_policy.md#br-vp-025-wrap-up-legal-name-lookup-climbs-from-member-to-relationship-policy-to-rate-account) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` | --- ## BR-VP-026 Policy header retrieval normalizes null financial amounts to zero | Field | Value | | --- | --- | | **Statement** | Policy-header retrieval wraps `current_balance` and `premium_estimate` in `NVL(..., 0)` before returning them. | | **Trigger** | `getpolicy` executes. | | **Enforcement** | The `SELECT` list uses `NVL(current_balance, 0)` and `NVL(premium_estimate, 0)`. | | **Outcome** | Callers receive zero instead of null for the retrieved balance and premium-estimate fields. | | **Stored procedure cross-reference** | [pk73094_policy_data.BR-VP-026](procedures/pk73094_policy_data.md#br-vp-026-policy-header-retrieval-normalizes-null-financial-amounts-to-zero) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk73094_policy_data.json` | --- ## BR-VP-027 Account-name lookup ranks active and parent policies first | Field | Value | | --- | --- | | **Statement** | Account-name lookup ranks policy candidates so active or pending-cancel statuses sort first, cancelled next, parent or self policies ahead of member policies, and later cancel dates ahead of earlier ones. | | **Trigger** | `get_account_name` executes with a non-null account number. | | **Enforcement** | The cursor query computes `policy_status_order`, `policy_relationship_order`, and `cancel_date`, orders by those values, and assigns the first fetched `legal_name` to `v_account_name`. | | **Outcome** | The returned account name comes from the preferred policy row for that account number. | | **Stored procedure cross-reference** | [pk73094_policy_data.BR-VP-027](procedures/pk73094_policy_data.md#br-vp-027-account-name-lookup-ranks-active-and-parent-policies-first) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk73094_policy_data.json` | --- ## BR-VP-028 Policy waiver count equals the number of waiver-detail rows for the policy | Field | Value | | --- | --- | | **Statement** | The waiver-count helper returns the number of `WAIVER_DETAIL` rows whose `POLICY_NUM` equals the input policy number. | | **Trigger** | `get_policy_waiver_count` executes. | | **Enforcement** | The function runs `SELECT COUNT(*) INTO v_count FROM WAIVER_DETAIL WHERE POLICY_NUM = PolicyNum` and returns `v_count`. | | **Outcome** | Callers receive a simple numeric waiver count for the policy. | | **Stored procedure cross-reference** | [pk82033_thirdparty.BR-VP-028](procedures/pk82033_thirdparty.md#br-vp-028-policy-waiver-count-equals-the-number-of-waiver-detail-rows-for-the-policy) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk82033_thirdparty.json` | --- ## BR-VP-029 Pending-cancel policies only derive the demanded status | Field | Value | | --- | --- | | **Statement** | When the policy status group is `PENDCA`, the function derives cancellation status only by checking for a demand row with `COLLECTION_ACT_RESPONSE_CODE IS NULL`. | | **Trigger** | `SP72268_GET_CANCEL_STATUS` executes for a policy whose `policy_status_group_code` is `PENDCA`. | | **Enforcement** | The main program routes `PENDCA` to `pending_cancel_status`, which calls only `look_for_demand_status`. | | **Outcome** | A pending-cancel policy returns `Demanded` when an open demand exists; otherwise it returns null. | | **Stored procedure cross-reference** | [sp72268_get_cancel_status.BR-VP-029](procedures/sp72268_get_cancel_status.md#br-vp-029-pending-cancel-policies-only-derive-the-demanded-status) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72268_get_cancel_status.json` | --- ## BR-VP-030 Cancelled policies use a fixed precedence order for derived collection statuses | Field | Value | | --- | --- | | **Statement** | For cancelled policies, the function derives collection status in a fixed order: Judgment, Lien, Defaulted, Demanded, Pending Audit, Pending PR, Closed w/Bal, and finally Closed. | | **Trigger** | `SP72268_GET_CANCEL_STATUS` executes for a policy whose `policy_status_group_code` is `CANCEL`. | | **Enforcement** | `cancel_status` calls the local derivation procedures only while `v_cancel_status` remains null, then defaults to `Closed`. | | **Outcome** | The first matching collection condition determines the returned cancellation status. | | **Stored procedure cross-reference** | [sp72268_get_cancel_status.BR-VP-030](procedures/sp72268_get_cancel_status.md#br-vp-030-cancelled-policies-use-a-fixed-precedence-order-for-derived-collection-statuses) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72268_get_cancel_status.json` | --- ## BR-VP-031 Defaulted and demanded statuses come from policy-demand response codes | Field | Value | | --- | --- | | **Statement** | The function derives `Defaulted` from a `POLICY_DEMAND` row whose `COLLECTION_ACT_RESPONSE_CODE = 'D'` and derives `Demanded` from a `POLICY_DEMAND` row whose response code is null. | | **Trigger** | `look_for_default_status` or `look_for_demand_status` executes. | | **Enforcement** | Each local procedure opens a cursor over `POLICY_DEMAND` with its respective response-code filter and assigns the corresponding status when a row is found. | | **Outcome** | Demand and default statuses are driven directly by the collection-action response state. | | **Stored procedure cross-reference** | [sp72268_get_cancel_status.BR-VP-031](procedures/sp72268_get_cancel_status.md#br-vp-031-defaulted-and-demanded-statuses-come-from-policy-demand-response-codes) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72268_get_cancel_status.json` | --- ## BR-VP-032 Application policies return saifQuote only when WCIS does not own the submission | Field | Value | | --- | --- | | **Statement** | For application-status policies, the function returns `saifQuote` only when `pk73102_policy.does_wcis_own_submission(in_policy_num) = 'N'` and no prior status has been assigned. | | **Trigger** | `SP72268_GET_CANCEL_STATUS` executes for a policy whose `policy_status_group_code` is `APP`. | | **Enforcement** | The main program routes `APP` to `saifquote_status`, which tests `pk73102_policy.does_wcis_own_submission` before assigning `v_cancel_status := 'saifQuote'`. | | **Outcome** | Application policies are labeled `saifQuote` only for non-WCIS-owned submissions. | | **Stored procedure cross-reference** | [sp72268_get_cancel_status.BR-VP-032](procedures/sp72268_get_cancel_status.md#br-vp-032-application-policies-return-saifquote-only-when-wcis-does-not-own-the-submission) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72268_get_cancel_status.json` | --- ## BR-VP-033 Information-page status is returned as Y or N | Field | Value | | --- | --- | | **Statement** | The function returns `Y` when `sp72253_info_page_printed(in_policy_num, in_policy_period_eff_date)` is true and `N` otherwise. | | **Trigger** | `SP72347_INFO_PAGE_PRINTED_YN` executes. | | **Enforcement** | The function checks the wrapped Boolean function in an `IF ... THEN RETURN 'Y' ELSE RETURN 'N' END IF` block. | | **Outcome** | Callers receive a character flag instead of a PL/SQL Boolean. | | **Stored procedure cross-reference** | [sp72347_info_page_printed_yn.BR-VP-033](procedures/sp72347_info_page_printed_yn.md#br-vp-033-information-page-status-is-returned-as-y-or-n) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72347_info_page_printed_yn.json` | --- ## BR-VP-034 Bound-policy status is returned as Y or N | Field | Value | | --- | --- | | **Statement** | The function returns `Y` when `sp72325_chk_if_policy_bound(in_policy_num, in_policy_period_eff_date)` is true and `N` otherwise. | | **Trigger** | `sp73089_get_policy_bound_flag` executes. | | **Enforcement** | The function sets `flag := 'Y'` or `N` based on the wrapped Boolean function and returns `flag`. | | **Outcome** | Callers receive a character flag instead of a PL/SQL Boolean. | | **Stored procedure cross-reference** | [sp73089_get_policy_bound_flag.BR-VP-034](procedures/sp73089_get_policy_bound_flag.md#br-vp-034-bound-policy-status-is-returned-as-y-or-n) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp73089_get_policy_bound_flag.json` | --- ## BR-VP-035 External-client email lookup requires trimmed case-insensitive matches on mail ID and last name | Field | Value | | --- | --- | | **Statement** | The function selects an email address only from `SYSTEM_USER_CONTRACTOR` rows whose trimmed `LAST_NAME` and `SYSTEM_USER_ID` both match the trimmed uppercase input values. | | **Trigger** | `sp73341_external_client_lookup` executes. | | **Enforcement** | The cursor query filters with `upper(trim(last_name)) = upper(trim(in_last_name))` and `upper(trim(system_user_id)) = upper(trim(in_mailid))`. | | **Outcome** | Only contractor rows matching both supplied identity values qualify. | | **Stored procedure cross-reference** | [sp73341_external_client_lookup.BR-VP-035](procedures/sp73341_external_client_lookup.md#br-vp-035-external-client-email-lookup-requires-trimmed-case-insensitive-matches-on-mail-id-and-last-name) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp73341_external_client_lookup.json` | --- ## BR-VP-036 External-client lookup returns null when no contractor row is found | Field | Value | | --- | --- | | **Statement** | When the contractor cursor fetch does not find a row, the function assigns `v_email_address := null` before returning. | | **Trigger** | `sp73341_external_client_lookup` executes and the cursor fetch is not found. | | **Enforcement** | The body checks `if su_contractor_cur%notfound then v_email_address := null; end if;`. | | **Outcome** | Callers receive null instead of an exception or stale email value when no contractor row matches. | | **Stored procedure cross-reference** | [sp73341_external_client_lookup.BR-VP-036](procedures/sp73341_external_client_lookup.md#br-vp-036-external-client-lookup-returns-null-when-no-contractor-row-is-found) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp73341_external_client_lookup.json` | --- ## BR-VP-037 Group policy-list retrieval selects a parent, group, or group-and-chapter branch from the input key | Field | Value | | --- | --- | | **Statement** | The group-policy-list procedure classifies the input as a parent-group query, a group-key query, or a group-key-plus-specific-chapter query before running the corresponding `SELECT` branch. | | **Trigger** | `cp_claimprofile_get_group_policy_list` executes. | | **Enforcement** | The procedure sets `@UseSql` by probing `WCS_EMPLOYER_GROUP.PARENT_GRP`, `GRP_ASSN_KEY`, and the presence of `@SpecificChapterCode`, then runs the matching `if @UseSql = ...` query block. | | **Outcome** | Only one query branch returns rows for the requested group context. | | **Stored procedure cross-reference** | [cp_claimprofile_get_group_policy_list.BR-VP-037](procedures/cp_claimprofile_get_group_policy_list.md#br-vp-037-group-policy-list-retrieval-selects-a-parent%2C-group%2C-or-group-and-chapter-branch-from-the-input-key) | | **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_claimprofile_get_group_policy_list.json` | --- ## BR-VP-038 Group policy-list retrieval keeps only active or still-effective memberships as of the report date | Field | Value | | --- | --- | | **Statement** | The group-policy-list procedure returns only rows where the policy is active and either the most recent coverage flag is set or the last in-group period still covers the report date; group-membership expiration must also be on or after `@rptDate`. | | **Trigger** | Any of the query branches in `cp_claimprofile_get_group_policy_list` executes. | | **Enforcement** | Each branch requires `wp.ACTV_PLCY_FL = 'Y'`, `pd_sum.GRP_MBRSHP_EXP_DT >= @rptDate`, and either `polPer.MOST_RECENT_PP_COV_FL = 'Y'` or the alternate last-in-group effective-date condition. | | **Outcome** | Expired policies and memberships outside the requested report date are excluded from the returned group book. | | **Stored procedure cross-reference** | [cp_claimprofile_get_group_policy_list.BR-VP-038](procedures/cp_claimprofile_get_group_policy_list.md#br-vp-038-group-policy-list-retrieval-keeps-only-active-or-still-effective-memberships-as-of-the-report-date) | | **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_claimprofile_get_group_policy_list.json` | --- ## BR-VP-039 Safety-consultant book retrieval supports exact and wildcard loss-control mail IDs | Field | Value | | --- | --- | | **Statement** | The safety-consultant book procedure matches `polTeam.LSS_MAIL_ID` either exactly or with `LIKE`, depending on whether the supplied `@losscontrol` value contains `%`. | | **Trigger** | `cp_claimprofile_get_sc_book` executes. | | **Enforcement** | The `WHERE` clause checks `charindex('%', @losscontrol)` and applies either equality or `LIKE rtrim(@losscontrol)`. | | **Outcome** | Callers can request a single consultant or a wildcard consultant pattern. | | **Stored procedure cross-reference** | [cp_claimprofile_get_sc_book.BR-VP-039](procedures/cp_claimprofile_get_sc_book.md#br-vp-039-safety-consultant-book-retrieval-supports-exact-and-wildcard-loss-control-mail-ids) | | **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_claimprofile_get_sc_book.json` | --- ## BR-VP-040 Safety-consultant book excludes flat-cancel and inactive policies | Field | Value | | --- | --- | | **Statement** | The safety-consultant book returns only active policies whose most recent policy period is not `FC` and whose loss-control mail ID is present. | | **Trigger** | `cp_claimprofile_get_sc_book` executes. | | **Enforcement** | The `WHERE` clause requires `T1.PLCY_PD_STAT_CD <> 'FC'`, `wPol.ACTV_PLCY_FL = 'Y'`, and non-null and non-empty `polTeam.Lss_mail_id`. | | **Outcome** | Flat-cancel, inactive, and unassigned policies are excluded from the consultants book. | | **Stored procedure cross-reference** | [cp_claimprofile_get_sc_book.BR-VP-040](procedures/cp_claimprofile_get_sc_book.md#br-vp-040-safety-consultant-book-excludes-flat-cancel-and-inactive-policies) | | **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_claimprofile_get_sc_book.json` | --- ## BR-VP-041 External-client lookup returns the latest active non-test registrant that matches the supplied ID and last name | Field | Value | | --- | --- | | **Statement** | The external-client lookup returns only rows whose trimmed `ID_NUM` and `USER_LAST_NAM` match the input values, whose `ACTIVE_FL = 'Y'`, whose `TEST_USER_FL = 'N'`, and whose `UPDATE_DATE` is greatest. | | **Trigger** | `cp_external_client_lookup` executes. | | **Enforcement** | The query filters on trimmed ID and last-name equality, active and non-test flags, orders by `reg.UPDATE_DATE DESC`, and selects `TOP 1 *`. | | **Outcome** | Callers receive the most recently updated active production registrant row for the supplied identity values. | | **Stored procedure cross-reference** | [cp_external_client_lookup.BR-VP-041](procedures/cp_external_client_lookup.md#br-vp-041-external-client-lookup-returns-the-latest-active-non-test-registrant-that-matches-the-supplied-id-and-last-name) | | **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_external_client_lookup.json` | --- ## BR-VP-042 Current certificate flag requires a certificate detail row that spans the policy period | Field | Value | | --- | --- | | **Statement** | The agent online policy list sets `currentCerts = 'Y'` only when at least one `wcs_certificate_detail` row exists for the policy whose certificate period covers the returned policy period. | | **Trigger** | `cp_get_agent_online_policy_list` returns an open-policy row. | | **Enforcement** | The open-policy `SELECT` computes `currentCerts` with a `CASE` expression over a correlated `COUNT(*)` subquery on `wcs_certificate_detail`. | | **Outcome** | Policies without a covering certificate-detail row return `currentCerts = 'N'`. | | **Stored procedure cross-reference** | [cp_get_agent_online_policy_list.BR-VP-042](procedures/cp_get_agent_online_policy_list.md#br-vp-042-current-certificate-flag-requires-a-certificate-detail-row-that-spans-the-policy-period) | | **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_get_agent_online_policy_list.json` | --- ## BR-VP-043 Viewable-rate flag requires rate-account ownership by the requesting agent or agency | Field | Value | | --- | --- | | **Statement** | The agent online policy list sets `viewableRate = 'Y'` only when the policy is its own rate account or the rate-account policy is owned by the requesting agent or agency; null rate accounts return `N`. | | **Trigger** | `cp_get_agent_online_policy_list` returns an open-policy row. | | **Enforcement** | The open-policy `SELECT` uses nested `CASE` logic over `rat_acct` and a correlated query that matches the requesting agent or agency based on `@ForAgency` and `@AgentNum`. | | **Outcome** | Rate-account detail is marked viewable only for the owning agent or agency. | | **Stored procedure cross-reference** | [cp_get_agent_online_policy_list.BR-VP-043](procedures/cp_get_agent_online_policy_list.md#br-vp-043-viewable-rate-flag-requires-rate-account-ownership-by-the-requesting-agent-or-agency) | | **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_get_agent_online_policy_list.json` | --- ## BR-VP-044 Closed policies are limited to the last five years | Field | Value | | --- | --- | | **Statement** | The procedures closed-policy branch includes only policies whose policy-period effective date is greater than or equal to `DateAdd(YYYY, -5, getDate())`. | | **Trigger** | The closed-policy `UNION` branch executes. | | **Enforcement** | The second `SELECT` adds `and polPer.plcy_pd_eff_dt >= DateAdd(YYYY, -5, getDate())`. | | **Outcome** | Older closed policies are excluded from the agent online list. | | **Stored procedure cross-reference** | [cp_get_agent_online_policy_list.BR-VP-044](procedures/cp_get_agent_online_policy_list.md#br-vp-044-closed-policies-are-limited-to-the-last-five-years) | | **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_get_agent_online_policy_list.json` | --- ## BR-VP-045 Pending-cancel flag is derived from the base policy status code | Field | Value | | --- | --- | | **Statement** | Both the open-policy and closed-policy branches derive `pendingCancel` as `Y` only when the joined base policy row has `PLCY_STAT_CD = '3'`. | | **Trigger** | `cp_get_agent_online_policy_list` returns any policy row. | | **Enforcement** | Each `SELECT` branch computes `pendingCancel` with `case when wPolBase.PLCY_STAT_CD = '3' then 'Y' else 'N' end`. | | **Outcome** | Returned policy rows carry an explicit pending-cancel indicator. | | **Stored procedure cross-reference** | [cp_get_agent_online_policy_list.BR-VP-045](procedures/cp_get_agent_online_policy_list.md#br-vp-045-pending-cancel-flag-is-derived-from-the-base-policy-status-code) | | **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_get_agent_online_policy_list.json` | --- ## BR-VP-046 Policy-in-group membership chooses a parent, group, or group-and-chapter query path | Field | Value | | --- | --- | | **Statement** | The membership-check procedure selects a parent-group, group-only, or group-plus-chapter query path by inspecting the supplied group key and whether a chapter string was supplied. | | **Trigger** | `cp_policy_in_group` executes. | | **Enforcement** | The procedure probes `WCS_EMPLOYER_GROUP` to set `@UseSql`, then runs the matching `if @UseSql = ...` `SELECT count(*)` block. | | **Outcome** | Only one membership-count query runs for the supplied key and chapter combination. | | **Stored procedure cross-reference** | [cp_policy_in_group.BR-VP-046](procedures/cp_policy_in_group.md#br-vp-046-policy-in-group-membership-chooses-a-parent%2C-group%2C-or-group-and-chapter-query-path) | | **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_policy_in_group.json` | --- ## BR-VP-047 Ordinary group-key detection excludes parent-group rows | Field | Value | | --- | --- | | **Statement** | When deciding whether the supplied key should be treated as a regular group key, the procedure excludes employer-group rows whose `GRP_TYP_CD = 'PARNT'`. | | **Trigger** | `cp_policy_in_group` evaluates the group-key branch. | | **Enforcement** | The `grp_assn_key` probe applies `AND grp_typ_cd <> 'PARNT'` before setting `@UseSql = 2`. | | **Outcome** | Parent-group-only keys are not misclassified as ordinary group keys. | | **Stored procedure cross-reference** | [cp_policy_in_group.BR-VP-047](procedures/cp_policy_in_group.md#br-vp-047-ordinary-group-key-detection-excludes-parent-group-rows) | | **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_policy_in_group.json` | --- ## BR-VP-048 Policy-in-group returns membership as a count and applies chapter filtering only in branch three | Field | Value | | --- | --- | | **Statement** | The membership-check procedure returns a numeric `howMany` count for every branch, and the chapter filter applies only in the group-plus-chapter branch. | | **Trigger** | `cp_policy_in_group` executes. | | **Enforcement** | Each branch selects `count(*) howMany`, while only the `@UseSql = 3` branch adds `GroupCheck.chapCd like rtrim(@chapters)`. | | **Outcome** | Callers receive a count-based membership answer, with chapter filtering limited to the explicit chapter branch. | | **Stored procedure cross-reference** | [cp_policy_in_group.BR-VP-048](procedures/cp_policy_in_group.md#br-vp-048-policy-in-group-returns-membership-as-a-count-and-applies-chapter-filtering-only-in-branch-three) | | **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_policy_in_group.json` | --- ## BR-VP-015 Specific and current requests prefer policy-period employer-liability rows | Field | Value | | --- | --- | | **Statement** | Specific and current limit requests first read `POLICY_PERIOD_EMP_LIAB` rows joined to `EMPLOYERS_LIABILITY_LIMITS` when an in-effect override exists for the requested policy period. | | **Trigger** | `getCurrentLimitsForPolicyNum` or `getSpecificLimitsForPolicyNum` executes for a policy period with matching `POLICY_PERIOD_EMP_LIAB` rows. | | **Enforcement** | Each procedure opens the cursor with a first `SELECT` or `UNION` branch over `POLICY_PERIOD_EMP_LIAB` and `EMPLOYERS_LIABILITY_LIMITS` before fallback branches. | | **Outcome** | The returned cursor exposes policy-specific employer-liability limits instead of standard defaults. | | **Stored procedure cross-reference** | [coveragelimits_pkg.BR-VP-015](procedures/coveragelimits_pkg.md#br-vp-015-specific-and-current-requests-prefer-policy-period-employer-liability-rows) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\coveragelimits_pkg.json` | --- ## BR-VP-016 Limit retrieval falls back to standard factor rows when no specific override exists | Field | Value | | --- | --- | | **Statement** | If no qualifying policy-specific employer-liability row exists, the package returns standard limit rows where `standard_limits_flag = 'Y'` and the factor effective dates cover the evaluation date. | | **Trigger** | A current, specific, future, or all-limits lookup executes without a matching specific period override. | | **Enforcement** | Fallback branches join `EMPLOYERS_LIABILITY_LIMITS` to `EMPLOYERS_LIABILITY_FACTORS` and require `standard_limits_flag = 'Y'` plus `NOT EXISTS` checks against `POLICY_PERIOD_EMP_LIAB`. | | **Outcome** | Callers still receive formatted limit values even when a policy period has no explicit override row. | | **Stored procedure cross-reference** | [coveragelimits_pkg.BR-VP-016](procedures/coveragelimits_pkg.md#br-vp-016-limit-retrieval-falls-back-to-standard-factor-rows-when-no-specific-override-exists) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\coveragelimits_pkg.json` | --- ## BR-VP-017 Future and historical lookups use the policy periods own effective context | Field | Value | | --- | --- | | **Statement** | Future and older-period lookups compare effective-dated factor rows against the requested policy period or its expiration date rather than always using `SYSDATE`. | | **Trigger** | `getFutureLimitsForPolicyNum` or the historical branches inside `getSpecificLimitsForPolicyNum` execute. | | **Enforcement** | `getFutureLimitsForPolicyNum` compares adjustment windows to `pp.policy_period_eff_date`, and historical branches in `getSpecificLimitsForPolicyNum` use `PeriodEffDate`, `policy_period_exp_date`, or `policy_period_non_wcis` dates. | | **Outcome** | Returned limits reflect the requested future or historical policy period, not only the current-day configuration. | | **Stored procedure cross-reference** | [coveragelimits_pkg.BR-VP-017](procedures/coveragelimits_pkg.md#br-vp-017-future-and-historical-lookups-use-the-policy-periods-own-effective-context) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\coveragelimits_pkg.json` | --- ## BR-VP-018 Gap detection ignores flat-cancel and prospect periods | Field | Value | | --- | --- | | **Statement** | Gap detection ignores policy periods with status `FC` or `PPROS` before comparing one periods cancel or expiration date to the next periods effective date. | | **Trigger** | `getgaps` or `getgapindicator` executes. | | **Enforcement** | Both routines filter out `policy_period_status_code` values `FC` and `PPROS` and compare only the remaining periods. | | **Outcome** | Only meaningful coverage gaps are returned or counted. | | **Stored procedure cross-reference** | [pk72846_view_policy.BR-VP-018](procedures/pk72846_view_policy.md#br-vp-018-gap-detection-ignores-flat-cancel-and-prospect-periods) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` | --- ## BR-VP-019 Assigned-risk policy periods mask risk-type and plan data until the info page prints | Field | Value | | --- | --- | | **Statement** | For policy periods with status `RF`, the package suppresses `risk_type_code` and plan description values until `SP72347_INFO_PAGE_PRINTED_YN(policy_num, policy_period_eff_date)` returns `Y`. | | **Trigger** | `getpolicyperiod` or `getpolicyperiods` retrieves an `RF` policy period. | | **Enforcement** | Both procedures use `CASE` expressions that return empty strings for `RF` rows when `SP72347_INFO_PAGE_PRINTED_YN` is not `Y`. | | **Outcome** | Assigned-risk policy-period detail stays masked until the information page is printed. | | **Stored procedure cross-reference** | [pk72846_view_policy.BR-VP-019](procedures/pk72846_view_policy.md#br-vp-019-assigned-risk-policy-periods-mask-risk-type-and-plan-data-until-the-info-page-prints) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` | --- ## BR-VP-020 Claim-address requests may fall back to the policy address | Field | Value | | --- | --- | | **Statement** | If a claim-address request finds no active claim location and `ingetmailaddrwhennotfound = 'Y'`, the procedure changes the requested location code to `POL` before opening the cursor. | | **Trigger** | `getpolicyaddress` executes for address type `CLM` and no current `CLM` location exists. | | **Enforcement** | The procedure counts qualifying `CLM` locations, then assigns `v_addr_type := 'POL'` when fallback is enabled and the count is zero. | | **Outcome** | Callers receive the policy mailing or location address instead of an empty claim-address result. | | **Stored procedure cross-reference** | [pk72846_view_policy.BR-VP-020](procedures/pk72846_view_policy.md#br-vp-020-claim-address-requests-may-fall-back-to-the-policy-address) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` | --- ## BR-VP-021 Address retrieval prefers current contacts and the latest effective location row | Field | Value | | --- | --- | | **Statement** | Policy-address lookups join only active contacts, constrain location rows to the latest qualifying effective date, and return a single top-ranked row; location-sequence lookups also rank contact types before applying `ROWNUM <= 1`. | | **Trigger** | `getpolicyaddress` or `getpolicyaddressbylocseqnum` executes. | | **Enforcement** | `getpolicyaddress` filters `contact_exp_date IS NULL`, constrains the effective date with a `MAX(loc_eff_date)` subquery, and limits the result with `ROWNUM <= 1`; `getpolicyaddressbylocseqnum` orders contact types and also applies `ROWNUM <= 1`. | | **Outcome** | The package returns one current address and contact record rather than multiple stale rows. | | **Stored procedure cross-reference** | [pk72846_view_policy.BR-VP-021](procedures/pk72846_view_policy.md#br-vp-021-address-retrieval-prefers-current-contacts-and-the-latest-effective-location-row) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` | --- ## BR-VP-022 Affiliate-type classification follows a fixed hierarchy | Field | Value | | --- | --- | | **Statement** | Affiliate-type resolution checks for manual policyholder affiliations first, then wrap and leasing relationships, then subaccount and state-agency relationships, then experience-combination memberships, and finally premium-discount combinations. | | **Trigger** | `getpolicyaffiliatetype` executes. | | **Enforcement** | The function evaluates each affiliation family in sequence and returns immediately on the first matching `SELECT ... INTO`. | | **Outcome** | A policy receives the first matching affiliate classification in the packages explicit precedence order. | | **Stored procedure cross-reference** | [pk72846_view_policy.BR-VP-022](procedures/pk72846_view_policy.md#br-vp-022-affiliate-type-classification-follows-a-fixed-hierarchy) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` | --- ## BR-VP-023 Parent-policy resolution depends on the resolved affiliate type | Field | Value | | --- | --- | | **Statement** | Parent-policy resolution chooses the parent lookup strategy from the affiliate type returned by `getpolicyaffiliatetype`, including manual affiliation, relationship, combination, premium-discount, or self-parent fallback paths. | | **Trigger** | `getparentpolicy` executes. | | **Enforcement** | The function branches on `v_affil_type` and queries `policy_relationship`, `policy_relationship_policy_num`, `rate_account`, `combo_policy_period`, or the input policy number. | | **Outcome** | Callers receive the appropriate parent policy number for the resolved affiliation model. | | **Stored procedure cross-reference** | [pk72846_view_policy.BR-VP-023](procedures/pk72846_view_policy.md#br-vp-023-parent-policy-resolution-depends-on-the-resolved-affiliate-type) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` | --- ## BR-VP-024 Affiliate total rows are optional and vary by affiliation family | Field | Value | | --- | --- | | **Statement** | Affiliate lists add a single totals row only when `in_include_affil_totals = 'TRUE'`, and the totals label and selection logic vary by affiliate family. | | **Trigger** | `getpolicyaffiliatelist` executes. | | **Enforcement** | The procedure builds `v_totals_description` and `v_affil_type`, then includes totals-row `UNION` branches only when `UPPER(in_include_affil_totals) = 'TRUE'`. | | **Outcome** | Callers can opt into or suppress the family-specific totals row for affiliate displays. | | **Stored procedure cross-reference** | [pk72846_view_policy.BR-VP-024](procedures/pk72846_view_policy.md#br-vp-024-affiliate-total-rows-are-optional-and-vary-by-affiliation-family) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` | --- ## BR-VP-025 Wrap-up legal-name lookup climbs from member to relationship policy to rate account | Field | Value | | --- | --- | | **Statement** | When a wrap-up member references a different relationship policy, `getwrapuplegalname` first loads that policy legal name and then, if a rate account exists, replaces it with the rate-account policy legal name. | | **Trigger** | `getwrapuplegalname` executes for a wrap-up member policy. | | **Enforcement** | The function selects `policy_relationship_policy_num` and `legal_name`, then conditionally selects the relationship policy and finally the `rate_account` policy. | | **Outcome** | The returned legal name reflects the top-level wrap-up or rate-account owner when available. | | **Stored procedure cross-reference** | [pk72846_view_policy.BR-VP-025](procedures/pk72846_view_policy.md#br-vp-025-wrap-up-legal-name-lookup-climbs-from-member-to-relationship-policy-to-rate-account) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` | --- ## BR-VP-026 Policy header retrieval normalizes null financial amounts to zero | Field | Value | | --- | --- | | **Statement** | Policy-header retrieval wraps `current_balance` and `premium_estimate` in `NVL(..., 0)` before returning them. | | **Trigger** | `getpolicy` executes. | | **Enforcement** | The `SELECT` list uses `NVL(current_balance, 0)` and `NVL(premium_estimate, 0)`. | | **Outcome** | Callers receive zero instead of null for the retrieved balance and premium-estimate fields. | | **Stored procedure cross-reference** | [pk73094_policy_data.BR-VP-026](procedures/pk73094_policy_data.md#br-vp-026-policy-header-retrieval-normalizes-null-financial-amounts-to-zero) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk73094_policy_data.json` | --- ## BR-VP-027 Account-name lookup ranks active and parent policies first | Field | Value | | --- | --- | | **Statement** | Account-name lookup ranks policy candidates so active or pending-cancel statuses sort first, cancelled next, parent or self policies ahead of member policies, and later cancel dates ahead of earlier ones. | | **Trigger** | `get_account_name` executes with a non-null account number. | | **Enforcement** | The cursor query computes `policy_status_order`, `policy_relationship_order`, and `cancel_date`, orders by those values, and assigns the first fetched `legal_name` to `v_account_name`. | | **Outcome** | The returned account name comes from the preferred policy row for that account number. | | **Stored procedure cross-reference** | [pk73094_policy_data.BR-VP-027](procedures/pk73094_policy_data.md#br-vp-027-account-name-lookup-ranks-active-and-parent-policies-first) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk73094_policy_data.json` | --- ## BR-VP-028 Policy waiver count equals the number of waiver-detail rows for the policy | Field | Value | | --- | --- | | **Statement** | The waiver-count helper returns the number of `WAIVER_DETAIL` rows whose `POLICY_NUM` equals the input policy number. | | **Trigger** | `get_policy_waiver_count` executes. | | **Enforcement** | The function runs `SELECT COUNT(*) INTO v_count FROM WAIVER_DETAIL WHERE POLICY_NUM = PolicyNum` and returns `v_count`. | | **Outcome** | Callers receive a simple numeric waiver count for the policy. | | **Stored procedure cross-reference** | [pk82033_thirdparty.BR-VP-028](procedures/pk82033_thirdparty.md#br-vp-028-policy-waiver-count-equals-the-number-of-waiver-detail-rows-for-the-policy) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk82033_thirdparty.json` | --- ## BR-VP-029 Pending-cancel policies only derive the demanded status | Field | Value | | --- | --- | | **Statement** | When the policy status group is `PENDCA`, the function derives cancellation status only by checking for a demand row with `COLLECTION_ACT_RESPONSE_CODE IS NULL`. | | **Trigger** | `SP72268_GET_CANCEL_STATUS` executes for a policy whose `policy_status_group_code` is `PENDCA`. | | **Enforcement** | The main program routes `PENDCA` to `pending_cancel_status`, which calls only `look_for_demand_status`. | | **Outcome** | A pending-cancel policy returns `Demanded` when an open demand exists; otherwise it returns null. | | **Stored procedure cross-reference** | [sp72268_get_cancel_status.BR-VP-029](procedures/sp72268_get_cancel_status.md#br-vp-029-pending-cancel-policies-only-derive-the-demanded-status) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72268_get_cancel_status.json` | --- ## BR-VP-030 Cancelled policies use a fixed precedence order for derived collection statuses | Field | Value | | --- | --- | | **Statement** | For cancelled policies, the function derives collection status in a fixed order: Judgment, Lien, Defaulted, Demanded, Pending Audit, Pending PR, Closed w/Bal, and finally Closed. | | **Trigger** | `SP72268_GET_CANCEL_STATUS` executes for a policy whose `policy_status_group_code` is `CANCEL`. | | **Enforcement** | `cancel_status` calls the local derivation procedures only while `v_cancel_status` remains null, then defaults to `Closed`. | | **Outcome** | The first matching collection condition determines the returned cancellation status. | | **Stored procedure cross-reference** | [sp72268_get_cancel_status.BR-VP-030](procedures/sp72268_get_cancel_status.md#br-vp-030-cancelled-policies-use-a-fixed-precedence-order-for-derived-collection-statuses) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72268_get_cancel_status.json` | --- ## BR-VP-031 Defaulted and demanded statuses come from policy-demand response codes | Field | Value | | --- | --- | | **Statement** | The function derives `Defaulted` from a `POLICY_DEMAND` row whose `COLLECTION_ACT_RESPONSE_CODE = 'D'` and derives `Demanded` from a `POLICY_DEMAND` row whose response code is null. | | **Trigger** | `look_for_default_status` or `look_for_demand_status` executes. | | **Enforcement** | Each local procedure opens a cursor over `POLICY_DEMAND` with its respective response-code filter and assigns the corresponding status when a row is found. | | **Outcome** | Demand and default statuses are driven directly by the collection-action response state. | | **Stored procedure cross-reference** | [sp72268_get_cancel_status.BR-VP-031](procedures/sp72268_get_cancel_status.md#br-vp-031-defaulted-and-demanded-statuses-come-from-policy-demand-response-codes) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72268_get_cancel_status.json` | --- ## BR-VP-032 Application policies return saifQuote only when WCIS does not own the submission | Field | Value | | --- | --- | | **Statement** | For application-status policies, the function returns `saifQuote` only when `pk73102_policy.does_wcis_own_submission(in_policy_num) = 'N'` and no prior status has been assigned. | | **Trigger** | `SP72268_GET_CANCEL_STATUS` executes for a policy whose `policy_status_group_code` is `APP`. | | **Enforcement** | The main program routes `APP` to `saifquote_status`, which tests `pk73102_policy.does_wcis_own_submission` before assigning `v_cancel_status := 'saifQuote'`. | | **Outcome** | Application policies are labeled `saifQuote` only for non-WCIS-owned submissions. | | **Stored procedure cross-reference** | [sp72268_get_cancel_status.BR-VP-032](procedures/sp72268_get_cancel_status.md#br-vp-032-application-policies-return-saifquote-only-when-wcis-does-not-own-the-submission) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72268_get_cancel_status.json` | --- ## BR-VP-033 Information-page status is returned as Y or N | Field | Value | | --- | --- | | **Statement** | The function returns `Y` when `sp72253_info_page_printed(in_policy_num, in_policy_period_eff_date)` is true and `N` otherwise. | | **Trigger** | `SP72347_INFO_PAGE_PRINTED_YN` executes. | | **Enforcement** | The function checks the wrapped Boolean function in an `IF ... THEN RETURN 'Y' ELSE RETURN 'N' END IF` block. | | **Outcome** | Callers receive a character flag instead of a PL/SQL Boolean. | | **Stored procedure cross-reference** | [sp72347_info_page_printed_yn.BR-VP-033](procedures/sp72347_info_page_printed_yn.md#br-vp-033-information-page-status-is-returned-as-y-or-n) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp72347_info_page_printed_yn.json` | --- ## BR-VP-034 Bound-policy status is returned as Y or N | Field | Value | | --- | --- | | **Statement** | The function returns `Y` when `sp72325_chk_if_policy_bound(in_policy_num, in_policy_period_eff_date)` is true and `N` otherwise. | | **Trigger** | `sp73089_get_policy_bound_flag` executes. | | **Enforcement** | The function sets `flag := 'Y'` or `N` based on the wrapped Boolean function and returns `flag`. | | **Outcome** | Callers receive a character flag instead of a PL/SQL Boolean. | | **Stored procedure cross-reference** | [sp73089_get_policy_bound_flag.BR-VP-034](procedures/sp73089_get_policy_bound_flag.md#br-vp-034-bound-policy-status-is-returned-as-y-or-n) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp73089_get_policy_bound_flag.json` | --- ## BR-VP-035 External-client email lookup requires trimmed case-insensitive matches on mail ID and last name | Field | Value | | --- | --- | | **Statement** | The function selects an email address only from `SYSTEM_USER_CONTRACTOR` rows whose trimmed `LAST_NAME` and `SYSTEM_USER_ID` both match the trimmed uppercase input values. | | **Trigger** | `sp73341_external_client_lookup` executes. | | **Enforcement** | The cursor query filters with `upper(trim(last_name)) = upper(trim(in_last_name))` and `upper(trim(system_user_id)) = upper(trim(in_mailid))`. | | **Outcome** | Only contractor rows matching both supplied identity values qualify. | | **Stored procedure cross-reference** | [sp73341_external_client_lookup.BR-VP-035](procedures/sp73341_external_client_lookup.md#br-vp-035-external-client-email-lookup-requires-trimmed-case-insensitive-matches-on-mail-id-and-last-name) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp73341_external_client_lookup.json` | --- ## BR-VP-036 External-client lookup returns null when no contractor row is found | Field | Value | | --- | --- | | **Statement** | When the contractor cursor fetch does not find a row, the function assigns `v_email_address := null` before returning. | | **Trigger** | `sp73341_external_client_lookup` executes and the cursor fetch is not found. | | **Enforcement** | The body checks `if su_contractor_cur%notfound then v_email_address := null; end if;`. | | **Outcome** | Callers receive null instead of an exception or stale email value when no contractor row matches. | | **Stored procedure cross-reference** | [sp73341_external_client_lookup.BR-VP-036](procedures/sp73341_external_client_lookup.md#br-vp-036-external-client-lookup-returns-null-when-no-contractor-row-is-found) | | **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp73341_external_client_lookup.json` | --- ## BR-VP-037 Group policy-list retrieval selects a parent, group, or group-and-chapter branch from the input key | Field | Value | | --- | --- | | **Statement** | The group-policy-list procedure classifies the input as a parent-group query, a group-key query, or a group-key-plus-specific-chapter query before running the corresponding `SELECT` branch. | | **Trigger** | `cp_claimprofile_get_group_policy_list` executes. | | **Enforcement** | The procedure sets `@UseSql` by probing `WCS_EMPLOYER_GROUP.PARENT_GRP`, `GRP_ASSN_KEY`, and the presence of `@SpecificChapterCode`, then runs the matching `if @UseSql = ...` query block. | | **Outcome** | Only one query branch returns rows for the requested group context. | | **Stored procedure cross-reference** | [cp_claimprofile_get_group_policy_list.BR-VP-037](procedures/cp_claimprofile_get_group_policy_list.md#br-vp-037-group-policy-list-retrieval-selects-a-parent%2C-group%2C-or-group-and-chapter-branch-from-the-input-key) | | **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_claimprofile_get_group_policy_list.json` | --- ## BR-VP-038 Group policy-list retrieval keeps only active or still-effective memberships as of the report date | Field | Value | | --- | --- | | **Statement** | The group-policy-list procedure returns only rows where the policy is active and either the most recent coverage flag is set or the last in-group period still covers the report date; group-membership expiration must also be on or after `@rptDate`. | | **Trigger** | Any of the query branches in `cp_claimprofile_get_group_policy_list` executes. | | **Enforcement** | Each branch requires `wp.ACTV_PLCY_FL = 'Y'`, `pd_sum.GRP_MBRSHP_EXP_DT >= @rptDate`, and either `polPer.MOST_RECENT_PP_COV_FL = 'Y'` or the alternate last-in-group effective-date condition. | | **Outcome** | Expired policies and memberships outside the requested report date are excluded from the returned group book. | | **Stored procedure cross-reference** | [cp_claimprofile_get_group_policy_list.BR-VP-038](procedures/cp_claimprofile_get_group_policy_list.md#br-vp-038-group-policy-list-retrieval-keeps-only-active-or-still-effective-memberships-as-of-the-report-date) | | **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_claimprofile_get_group_policy_list.json` | --- ## BR-VP-039 Safety-consultant book retrieval supports exact and wildcard loss-control mail IDs | Field | Value | | --- | --- | | **Statement** | The safety-consultant book procedure matches `polTeam.LSS_MAIL_ID` either exactly or with `LIKE`, depending on whether the supplied `@losscontrol` value contains `%`. | | **Trigger** | `cp_claimprofile_get_sc_book` executes. | | **Enforcement** | The `WHERE` clause checks `charindex('%', @losscontrol)` and applies either equality or `LIKE rtrim(@losscontrol)`. | | **Outcome** | Callers can request a single consultant or a wildcard consultant pattern. | | **Stored procedure cross-reference** | [cp_claimprofile_get_sc_book.BR-VP-039](procedures/cp_claimprofile_get_sc_book.md#br-vp-039-safety-consultant-book-retrieval-supports-exact-and-wildcard-loss-control-mail-ids) | | **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_claimprofile_get_sc_book.json` | --- ## BR-VP-040 Safety-consultant book excludes flat-cancel and inactive policies | Field | Value | | --- | --- | | **Statement** | The safety-consultant book returns only active policies whose most recent policy period is not `FC` and whose loss-control mail ID is present. | | **Trigger** | `cp_claimprofile_get_sc_book` executes. | | **Enforcement** | The `WHERE` clause requires `T1.PLCY_PD_STAT_CD <> 'FC'`, `wPol.ACTV_PLCY_FL = 'Y'`, and non-null and non-empty `polTeam.Lss_mail_id`. | | **Outcome** | Flat-cancel, inactive, and unassigned policies are excluded from the consultants book. | | **Stored procedure cross-reference** | [cp_claimprofile_get_sc_book.BR-VP-040](procedures/cp_claimprofile_get_sc_book.md#br-vp-040-safety-consultant-book-excludes-flat-cancel-and-inactive-policies) | | **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_claimprofile_get_sc_book.json` | --- ## BR-VP-041 External-client lookup returns the latest active non-test registrant that matches the supplied ID and last name | Field | Value | | --- | --- | | **Statement** | The external-client lookup returns only rows whose trimmed `ID_NUM` and `USER_LAST_NAM` match the input values, whose `ACTIVE_FL = 'Y'`, whose `TEST_USER_FL = 'N'`, and whose `UPDATE_DATE` is greatest. | | **Trigger** | `cp_external_client_lookup` executes. | | **Enforcement** | The query filters on trimmed ID and last-name equality, active and non-test flags, orders by `reg.UPDATE_DATE DESC`, and selects `TOP 1 *`. | | **Outcome** | Callers receive the most recently updated active production registrant row for the supplied identity values. | | **Stored procedure cross-reference** | [cp_external_client_lookup.BR-VP-041](procedures/cp_external_client_lookup.md#br-vp-041-external-client-lookup-returns-the-latest-active-non-test-registrant-that-matches-the-supplied-id-and-last-name) | | **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_external_client_lookup.json` | --- ## BR-VP-042 Current certificate flag requires a certificate detail row that spans the policy period | Field | Value | | --- | --- | | **Statement** | The agent online policy list sets `currentCerts = 'Y'` only when at least one `wcs_certificate_detail` row exists for the policy whose certificate period covers the returned policy period. | | **Trigger** | `cp_get_agent_online_policy_list` returns an open-policy row. | | **Enforcement** | The open-policy `SELECT` computes `currentCerts` with a `CASE` expression over a correlated `COUNT(*)` subquery on `wcs_certificate_detail`. | | **Outcome** | Policies without a covering certificate-detail row return `currentCerts = 'N'`. | | **Stored procedure cross-reference** | [cp_get_agent_online_policy_list.BR-VP-042](procedures/cp_get_agent_online_policy_list.md#br-vp-042-current-certificate-flag-requires-a-certificate-detail-row-that-spans-the-policy-period) | | **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_get_agent_online_policy_list.json` | --- ## BR-VP-043 Viewable-rate flag requires rate-account ownership by the requesting agent or agency | Field | Value | | --- | --- | | **Statement** | The agent online policy list sets `viewableRate = 'Y'` only when the policy is its own rate account or the rate-account policy is owned by the requesting agent or agency; null rate accounts return `N`. | | **Trigger** | `cp_get_agent_online_policy_list` returns an open-policy row. | | **Enforcement** | The open-policy `SELECT` uses nested `CASE` logic over `rat_acct` and a correlated query that matches the requesting agent or agency based on `@ForAgency` and `@AgentNum`. | | **Outcome** | Rate-account detail is marked viewable only for the owning agent or agency. | | **Stored procedure cross-reference** | [cp_get_agent_online_policy_list.BR-VP-043](procedures/cp_get_agent_online_policy_list.md#br-vp-043-viewable-rate-flag-requires-rate-account-ownership-by-the-requesting-agent-or-agency) | | **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_get_agent_online_policy_list.json` | --- ## BR-VP-044 Closed policies are limited to the last five years | Field | Value | | --- | --- | | **Statement** | The procedures closed-policy branch includes only policies whose policy-period effective date is greater than or equal to `DateAdd(YYYY, -5, getDate())`. | | **Trigger** | The closed-policy `UNION` branch executes. | | **Enforcement** | The second `SELECT` adds `and polPer.plcy_pd_eff_dt >= DateAdd(YYYY, -5, getDate())`. | | **Outcome** | Older closed policies are excluded from the agent online list. | | **Stored procedure cross-reference** | [cp_get_agent_online_policy_list.BR-VP-044](procedures/cp_get_agent_online_policy_list.md#br-vp-044-closed-policies-are-limited-to-the-last-five-years) | | **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_get_agent_online_policy_list.json` | --- ## BR-VP-045 Pending-cancel flag is derived from the base policy status code | Field | Value | | --- | --- | | **Statement** | Both the open-policy and closed-policy branches derive `pendingCancel` as `Y` only when the joined base policy row has `PLCY_STAT_CD = '3'`. | | **Trigger** | `cp_get_agent_online_policy_list` returns any policy row. | | **Enforcement** | Each `SELECT` branch computes `pendingCancel` with `case when wPolBase.PLCY_STAT_CD = '3' then 'Y' else 'N' end`. | | **Outcome** | Returned policy rows carry an explicit pending-cancel indicator. | | **Stored procedure cross-reference** | [cp_get_agent_online_policy_list.BR-VP-045](procedures/cp_get_agent_online_policy_list.md#br-vp-045-pending-cancel-flag-is-derived-from-the-base-policy-status-code) | | **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_get_agent_online_policy_list.json` | --- ## BR-VP-046 Policy-in-group membership chooses a parent, group, or group-and-chapter query path | Field | Value | | --- | --- | | **Statement** | The membership-check procedure selects a parent-group, group-only, or group-plus-chapter query path by inspecting the supplied group key and whether a chapter string was supplied. | | **Trigger** | `cp_policy_in_group` executes. | | **Enforcement** | The procedure probes `WCS_EMPLOYER_GROUP` to set `@UseSql`, then runs the matching `if @UseSql = ...` `SELECT count(*)` block. | | **Outcome** | Only one membership-count query runs for the supplied key and chapter combination. | | **Stored procedure cross-reference** | [cp_policy_in_group.BR-VP-046](procedures/cp_policy_in_group.md#br-vp-046-policy-in-group-membership-chooses-a-parent%2C-group%2C-or-group-and-chapter-query-path) | | **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_policy_in_group.json` | --- ## BR-VP-047 Ordinary group-key detection excludes parent-group rows | Field | Value | | --- | --- | | **Statement** | When deciding whether the supplied key should be treated as a regular group key, the procedure excludes employer-group rows whose `GRP_TYP_CD = 'PARNT'`. | | **Trigger** | `cp_policy_in_group` evaluates the group-key branch. | | **Enforcement** | The `grp_assn_key` probe applies `AND grp_typ_cd <> 'PARNT'` before setting `@UseSql = 2`. | | **Outcome** | Parent-group-only keys are not misclassified as ordinary group keys. | | **Stored procedure cross-reference** | [cp_policy_in_group.BR-VP-047](procedures/cp_policy_in_group.md#br-vp-047-ordinary-group-key-detection-excludes-parent-group-rows) | | **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_policy_in_group.json` | --- ## BR-VP-048 Policy-in-group returns membership as a count and applies chapter filtering only in branch three | Field | Value | | --- | --- | | **Statement** | The membership-check procedure returns a numeric `howMany` count for every branch, and the chapter filter applies only in the group-plus-chapter branch. | | **Trigger** | `cp_policy_in_group` executes. | | **Enforcement** | Each branch selects `count(*) howMany`, while only the `@UseSql = 3` branch adds `GroupCheck.chapCd like rtrim(@chapters)`. | | **Outcome** | Callers receive a count-based membership answer, with chapter filtering limited to the explicit chapter branch. | | **Stored procedure cross-reference** | [cp_policy_in_group.BR-VP-048](procedures/cp_policy_in_group.md#br-vp-048-policy-in-group-returns-membership-as-a-count-and-applies-chapter-filtering-only-in-branch-three) | | **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_policy_in_group.json` | ## Stored procedure business rules Business rules extracted directly from the retrieved Oracle and SQL Server procedure and package source are summarized below. | Rule ID | Rule | Source | Link | | --- | --- | --- | --- | | [BR-VP-015](#br-vp-015-specific-and-current-requests-prefer-policy-period-employer-liability-rows) | Specific and current limit lookups return `POLICY_PERIOD_EMP_LIAB` rows when an in-effect policy-period override exists. | `coveragelimits_pkg` | [Details](procedures/coveragelimits_pkg.md#br-vp-015-specific-and-current-requests-prefer-policy-period-employer-liability-rows) | | [BR-VP-016](#br-vp-016-limit-retrieval-falls-back-to-standard-factor-rows-when-no-specific-override-exists) | When no period-specific limit record qualifies, the package falls back to standard-limit rows from `EMPLOYERS_LIABILITY_FACTORS`. | `coveragelimits_pkg` | [Details](procedures/coveragelimits_pkg.md#br-vp-016-limit-retrieval-falls-back-to-standard-factor-rows-when-no-specific-override-exists) | | [BR-VP-017](#br-vp-017-future-and-historical-lookups-use-the-policy-periods-own-effective-context) | Future and historical limit lookups evaluate factors against the requested policy-period dates instead of `SYSDATE`. | `coveragelimits_pkg` | [Details](procedures/coveragelimits_pkg.md#br-vp-017-future-and-historical-lookups-use-the-policy-periods-own-effective-context) | | [BR-VP-018](#br-vp-018-gap-detection-ignores-flat-cancel-and-prospect-periods) | Coverage-gap detection excludes flat-cancel and `PPROS` periods before comparing adjacent periods. | `pk72846_view_policy` | [Details](procedures/pk72846_view_policy.md#br-vp-018-gap-detection-ignores-flat-cancel-and-prospect-periods) | | [BR-VP-019](#br-vp-019-assigned-risk-policy-periods-mask-risk-type-and-plan-data-until-the-info-page-prints) | `RF` policy periods blank risk-type and plan output until `SP72347_INFO_PAGE_PRINTED_YN` returns `Y`. | `pk72846_view_policy` | [Details](procedures/pk72846_view_policy.md#br-vp-019-assigned-risk-policy-periods-mask-risk-type-and-plan-data-until-the-info-page-prints) | | [BR-VP-020](#br-vp-020-claim-address-requests-may-fall-back-to-the-policy-address) | When callers allow fallback and no active `CLM` location exists, address retrieval switches to `POL`. | `pk72846_view_policy` | [Details](procedures/pk72846_view_policy.md#br-vp-020-claim-address-requests-may-fall-back-to-the-policy-address) | | [BR-VP-021](#br-vp-021-address-retrieval-prefers-current-contacts-and-the-latest-effective-location-row) | Address lookups limit results to active contacts and the most recent effective matching location, then return only the first ranked row. | `pk72846_view_policy` | [Details](procedures/pk72846_view_policy.md#br-vp-021-address-retrieval-prefers-current-contacts-and-the-latest-effective-location-row) | | [BR-VP-022](#br-vp-022-affiliate-type-classification-follows-a-fixed-hierarchy) | Affiliate detection checks policyholder or manual affiliations first, then relationship, combination, and premium-discount affiliations. | `pk72846_view_policy` | [Details](procedures/pk72846_view_policy.md#br-vp-022-affiliate-type-classification-follows-a-fixed-hierarchy) | | [BR-VP-023](#br-vp-023-parent-policy-resolution-depends-on-the-resolved-affiliate-type) | Parent-policy lookup chooses `policy_relationship`, `policy_relationship_policy_num`, `rate_account`, or premium-member logic based on affiliate type. | `pk72846_view_policy` | [Details](procedures/pk72846_view_policy.md#br-vp-023-parent-policy-resolution-depends-on-the-resolved-affiliate-type) | | [BR-VP-024](#br-vp-024-affiliate-total-rows-are-optional-and-vary-by-affiliation-family) | Affiliate lists add a totals row only when `in_include_affil_totals = 'TRUE'`, using family-specific labels. | `pk72846_view_policy` | [Details](procedures/pk72846_view_policy.md#br-vp-024-affiliate-total-rows-are-optional-and-vary-by-affiliation-family) | | [BR-VP-025](#br-vp-025-wrap-up-legal-name-lookup-climbs-from-member-to-relationship-policy-to-rate-account) | Wrap-up legal-name resolution starts on the member row, then follows the relationship policy and rate account when present. | `pk72846_view_policy` | [Details](procedures/pk72846_view_policy.md#br-vp-025-wrap-up-legal-name-lookup-climbs-from-member-to-relationship-policy-to-rate-account) | | [BR-VP-026](#br-vp-026-policy-header-retrieval-normalizes-null-financial-amounts-to-zero) | `getpolicy` converts null `current_balance` and `premium_estimate` values to zero before returning them. | `pk73094_policy_data` | [Details](procedures/pk73094_policy_data.md#br-vp-026-policy-header-retrieval-normalizes-null-financial-amounts-to-zero) | | [BR-VP-027](#br-vp-027-account-name-lookup-ranks-active-and-parent-policies-first) | `get_account_name` orders candidate policies by status, parent and self relationship, and latest cancel date before taking the first row. | `pk73094_policy_data` | [Details](procedures/pk73094_policy_data.md#br-vp-027-account-name-lookup-ranks-active-and-parent-policies-first) | | [BR-VP-028](#br-vp-028-policy-waiver-count-equals-the-number-of-waiver-detail-rows-for-the-policy) | `get_policy_waiver_count` returns `COUNT(*)` from `WAIVER_DETAIL` for the supplied policy number. | `pk82033_thirdparty` | [Details](procedures/pk82033_thirdparty.md#br-vp-028-policy-waiver-count-equals-the-number-of-waiver-detail-rows-for-the-policy) | | [BR-VP-029](#br-vp-029-pending-cancel-policies-only-derive-the-demanded-status) | Policies in status group `PENDCA` run only the demand-status branch. | `sp72268_get_cancel_status` | [Details](procedures/sp72268_get_cancel_status.md#br-vp-029-pending-cancel-policies-only-derive-the-demanded-status) | | [BR-VP-030](#br-vp-030-cancelled-policies-use-a-fixed-precedence-order-for-derived-collection-statuses) | Cancelled policies evaluate Judgment, Lien, Defaulted, Demanded, Pending Audit, Pending PR, Closed w/Bal, then Closed. | `sp72268_get_cancel_status` | [Details](procedures/sp72268_get_cancel_status.md#br-vp-030-cancelled-policies-use-a-fixed-precedence-order-for-derived-collection-statuses) | | [BR-VP-031](#br-vp-031-defaulted-and-demanded-statuses-come-from-policy-demand-response-codes) | `Defaulted` requires response code `D`; `Demanded` requires a policy-demand row with a null response code. | `sp72268_get_cancel_status` | [Details](procedures/sp72268_get_cancel_status.md#br-vp-031-defaulted-and-demanded-statuses-come-from-policy-demand-response-codes) | | [BR-VP-032](#br-vp-032-application-policies-return-saifquote-only-when-wcis-does-not-own-the-submission) | Status group `APP` yields `saifQuote` only when `pk73102_policy.does_wcis_own_submission(...) = 'N'`. | `sp72268_get_cancel_status` | [Details](procedures/sp72268_get_cancel_status.md#br-vp-032-application-policies-return-saifquote-only-when-wcis-does-not-own-the-submission) | | [BR-VP-033](#br-vp-033-information-page-status-is-returned-as-y-or-n) | The wrapper converts `sp72253_info_page_printed(...)` Boolean output into `Y` or `N`. | `sp72347_info_page_printed_yn` | [Details](procedures/sp72347_info_page_printed_yn.md#br-vp-033-information-page-status-is-returned-as-y-or-n) | | [BR-VP-034](#br-vp-034-bound-policy-status-is-returned-as-y-or-n) | The wrapper converts `sp72325_chk_if_policy_bound(...)` Boolean output into `Y` or `N`. | `sp73089_get_policy_bound_flag` | [Details](procedures/sp73089_get_policy_bound_flag.md#br-vp-034-bound-policy-status-is-returned-as-y-or-n) | | [BR-VP-035](#br-vp-035-external-client-email-lookup-requires-trimmed-case-insensitive-matches-on-mail-id-and-last-name) | The function matches `SYSTEM_USER_CONTRACTOR` rows only when both `system_user_id` and `last_name` match the trimmed uppercase inputs. | `sp73341_external_client_lookup` | [Details](procedures/sp73341_external_client_lookup.md#br-vp-035-external-client-email-lookup-requires-trimmed-case-insensitive-matches-on-mail-id-and-last-name) | | [BR-VP-036](#br-vp-036-external-client-lookup-returns-null-when-no-contractor-row-is-found) | If the cursor fetch finds no row, the function explicitly returns null. | `sp73341_external_client_lookup` | [Details](procedures/sp73341_external_client_lookup.md#br-vp-036-external-client-lookup-returns-null-when-no-contractor-row-is-found) | | [BR-VP-037](#br-vp-037-group-policy-list-retrieval-selects-a-parent%2C-group%2C-or-group-and-chapter-branch-from-the-input-key) | `@UseSql` is set to 1, 2, or 3 depending on whether the input resolves as a parent group, a group key, or a group key with chapters. | `cp_claimprofile_get_group_policy_list` | [Details](procedures/cp_claimprofile_get_group_policy_list.md#br-vp-037-group-policy-list-retrieval-selects-a-parent%2C-group%2C-or-group-and-chapter-branch-from-the-input-key) | | [BR-VP-038](#br-vp-038-group-policy-list-retrieval-keeps-only-active-or-still-effective-memberships-as-of-the-report-date) | Rows must be active or still within the report-date membership window, and membership expiration must be on or after `@rptDate`. | `cp_claimprofile_get_group_policy_list` | [Details](procedures/cp_claimprofile_get_group_policy_list.md#br-vp-038-group-policy-list-retrieval-keeps-only-active-or-still-effective-memberships-as-of-the-report-date) | | [BR-VP-039](#br-vp-039-safety-consultant-book-retrieval-supports-exact-and-wildcard-loss-control-mail-ids) | The procedure accepts either an exact mail ID or a wildcard pattern depending on whether the input contains `%`. | `cp_claimprofile_get_sc_book` | [Details](procedures/cp_claimprofile_get_sc_book.md#br-vp-039-safety-consultant-book-retrieval-supports-exact-and-wildcard-loss-control-mail-ids) | | [BR-VP-040](#br-vp-040-safety-consultant-book-excludes-flat-cancel-and-inactive-policies) | Only active policies whose most recent period is not flat-cancel and whose loss-control mail ID is populated are returned. | `cp_claimprofile_get_sc_book` | [Details](procedures/cp_claimprofile_get_sc_book.md#br-vp-040-safety-consultant-book-excludes-flat-cancel-and-inactive-policies) | | [BR-VP-041](#br-vp-041-external-client-lookup-returns-the-latest-active-non-test-registrant-that-matches-the-supplied-id-and-last-name) | The procedure returns `TOP 1` from `Net_Registrant` after trimmed ID and last-name matching, restricted to active non-test rows and ordered by latest update date. | `cp_external_client_lookup` | [Details](procedures/cp_external_client_lookup.md#br-vp-041-external-client-lookup-returns-the-latest-active-non-test-registrant-that-matches-the-supplied-id-and-last-name) | | [BR-VP-042](#br-vp-042-current-certificate-flag-requires-a-certificate-detail-row-that-spans-the-policy-period) | `currentCerts` is `Y` only when a certificate-detail row covers the returned policy period. | `cp_get_agent_online_policy_list` | [Details](procedures/cp_get_agent_online_policy_list.md#br-vp-042-current-certificate-flag-requires-a-certificate-detail-row-that-spans-the-policy-period) | | [BR-VP-043](#br-vp-043-viewable-rate-flag-requires-rate-account-ownership-by-the-requesting-agent-or-agency) | `viewableRate` is `Y` only when the rate account is self or the rate-account policy is owned by the requesting agent or agency. | `cp_get_agent_online_policy_list` | [Details](procedures/cp_get_agent_online_policy_list.md#br-vp-043-viewable-rate-flag-requires-rate-account-ownership-by-the-requesting-agent-or-agency) | | [BR-VP-044](#br-vp-044-closed-policies-are-limited-to-the-last-five-years) | The closed-policy branch returns only policies whose current-period effective date is within the last five years. | `cp_get_agent_online_policy_list` | [Details](procedures/cp_get_agent_online_policy_list.md#br-vp-044-closed-policies-are-limited-to-the-last-five-years) | | [BR-VP-045](#br-vp-045-pending-cancel-flag-is-derived-from-the-base-policy-status-code) | `pendingCancel` is `Y` when `WCS_POLICY.PLCY_STAT_CD = '3'` and `N` otherwise. | `cp_get_agent_online_policy_list` | [Details](procedures/cp_get_agent_online_policy_list.md#br-vp-045-pending-cancel-flag-is-derived-from-the-base-policy-status-code) | | [BR-VP-046](#br-vp-046-policy-in-group-membership-chooses-a-parent%2C-group%2C-or-group-and-chapter-query-path) | `@UseSql` is set to 1, 2, or 3 based on whether the input key matches a parent group, a group, or a group plus chapter filter. | `cp_policy_in_group` | [Details](procedures/cp_policy_in_group.md#br-vp-046-policy-in-group-membership-chooses-a-parent%2C-group%2C-or-group-and-chapter-query-path) | | [BR-VP-047](#br-vp-047-ordinary-group-key-detection-excludes-parent-group-rows) | Group-key detection ignores rows whose `GRP_TYP_CD = 'PARNT'`. | `cp_policy_in_group` | [Details](procedures/cp_policy_in_group.md#br-vp-047-ordinary-group-key-detection-excludes-parent-group-rows) | | [BR-VP-048](#br-vp-048-policy-in-group-returns-membership-as-a-count-and-applies-chapter-filtering-only-in-branch-three) | Each branch returns `count(*)`; the chapter filter is added only when `@UseSql = 3`. | `cp_policy_in_group` | [Details](procedures/cp_policy_in_group.md#br-vp-048-policy-in-group-returns-membership-as-a-count-and-applies-chapter-filtering-only-in-branch-three) | ## Notes - No concrete, domain-specific inherited business rules were found for this library's framework base classes (`clsBusinessBase`, `clsBusinessCollection`, `clsBusinessCollectionBase`, `clsAddress`) — the retrieved framework documentation describes only generic rule-registration/broken-rule-tracking mechanisms (via `clsRulesManager`/`IDataErrorInfo`), not concrete rules attributable to a specific statement, trigger, and outcome. No `Inherited business rules` section is included per the omit-if-empty guidance. - Mark inferred statements with `*` and unknown statements with `**`. Do not use bold inline confidence labels. ## Related documentation - [Index](index.md) - [Overview](overview.md) - [Architecture](architecture.md) - [Dependencies](dependencies.md) - [Operations and processes](operations-processes.md) - [Database tables](database-tables.md) - [SQL catalog](sql/index.md) - [API reference](api/index.md) > **Note:** Statements marked with `*` were inferred from code structure, naming > conventions, or contextual evidence where direct confirmation was not available > in source artifacts. Statements marked with `**` could not be confirmed; treat > as unknown until verified. ## Sources - `SAIF.Business.View.Policy\clsPolicyData.vb` - `SAIF.Business.View.Policy\clsPolicySecurity.vb` - `SAIF.Business.View.Policy\clsAgency.vb` - `SAIF.Business.View.Policy\clsPolicyAddress.vb` - `SAIF.Business.View.Policy\clsTeamAssignment.vb` - `SAIF.Business.View.Policy\clsAffiliatedPolicyCollection.vb` - `SAIF.Business.View.Policy\clsNonEmployerAccount.vb`